using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Collections;
using System.Web.UI.HtmlControls;
using System.Globalization;
using System.Web.Script.Services;
using System.Web.Services;

/// <summary>
/// A page for managing resources for applications
/// </summary>
public partial class Resources : System.Web.UI.Page
{
    /// <summary>
    /// Indexes of the controls in each dynamic table row on the 'Add Resource' form
    /// </summary>
    private static class AddFormControlIndexes {
        public const int txtTranslation = 1, // multiview->view->textbox
                         txtTranslationComment = 1,
                         btnFindSimilar = 2;
    }
    /// <summary>
    /// Index of the controls in each dynamic cell
    /// </summary>
    private static class SelectedRowControlIndexes {
        public const int txtTranslation = 1,
                         btnFindSimilar = 3,
                         btnGoogle = 4;
    }

    private Database db = new Database();
    private const int editButtonColumNo = 3;
    private SortedList<int, string> resourceIds = new SortedList<int, string>();
    private StringBuilder jQueryBuffer;

    /// <summary>
    /// Displays available applications and handles JavaScript for the full-window tranlation editor
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Load(object sender, EventArgs e)
    {
        jQueryBuffer = new StringBuilder();
        if(!Page.IsPostBack) {
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT id, displayname FROM " + db.TablePrefix + "applications ORDER BY name ASC";
                Database.DataAdapter adapter = new Database.DataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                radApplication.DataSource = ds;
                radApplication.DataValueField = "id";
                radApplication.DataTextField = "displayname";
                radApplication.DataBind();
                if(radApplication.Items.Count > 0) {
                    radApplication.SelectedIndex = 0;
                    ShowLanguages(null, EventArgs.Empty);
                    mltMain.ActiveViewIndex = 1;
                }
            }
            Session.Remove("crossTabColumns");
            Session.Remove("defaultLanguage");
            Session.Remove("colIndexes");
        } else {
            Page.ClientScript.RegisterClientScriptInclude("json", ResolveUrl("~/js/jquery.json-1.3.min.js"));
            Page.ClientScript.RegisterClientScriptInclude("blockUI", ResolveUrl("~/js/jquery.block.UI.min.js"));
            Page.ClientScript.RegisterClientScriptInclude("resourcejs", ResolveUrl("~/js/resources.js"));
            Master.RenderJSArrayWithCliendIds(fullsizeholder, cboAddResourceId);
            if(mltMain.ActiveViewIndex == 2) {
                PopulateSelectedLanguages(true, false, false);
            }
        }
        ScriptManager.RegisterStartupScript(this, typeof(Page), "nojs", "$(function(){ $('#" + btnRefresh.ClientID + "').remove(); });", true);
        pnlAddedElement.Visible = false;
    }

    /// <summary>
    /// Closes the database connection when rendering is done.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_Unload(object sender, EventArgs e) {
        if(db != null && db.Connected) {
            db.CloseConnection();
        }
    }

    /// <summary>
    /// Outputs the jQuery buffer to JavaScript
    /// Page_PreRender > Controls Pre_Render > Page_SaveStateComplete
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void Page_SaveStateComplete(object sender, EventArgs e) {
        jQueryBuffer.Insert(0, "var prm = Sys.WebForms.PageRequestManager.getInstance(); prm.add_endRequest(function() { \n");
        jQueryBuffer.Append("});\n");
        ScriptManager.RegisterStartupScript(this, typeof(Page), "jquery", jQueryBuffer.ToString(), true);
    }
    
    /// <summary>
    /// Shows the languages and bundles for the application selected.
    /// </summary>
    /// <param name="sender">The sender.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void ShowLanguages(object sender, EventArgs e) {
        if(radApplication.SelectedValue == null) {
            return;
        }
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "SELECT languages FROM " + db.TablePrefix + "applications WHERE id = @p";
            cmd.AddParameterWithValue("@p", radApplication.SelectedValue);
            ListComparer cmp = new ListComparer();
            List<ListItem> tempLanguages = new List<ListItem>(),
                           tempBundles = new List<ListItem>();
            lstLanguages.Items.Clear();
            string rawLanguages = cmd.ExecuteScalar().ToString();
            foreach(string abbrev in rawLanguages.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) {
                ListItem itm = null;
                try {
                    itm = new ListItem(CultureInfo.GetCultureInfo(abbrev).EnglishName, abbrev);
                } catch {
                    itm = new ListItem(abbrev);
                }
                itm.Selected = true;
                tempLanguages.Add(itm);
            }
            tempLanguages.Sort(cmp);
            lstLanguages.Items.AddRange(tempLanguages.ToArray());
            if(lstLanguages.Items.Count == 0) {
                ListItem itm = new ListItem("No languages found", "");
                itm.Selected = true;
                lstLanguages.Items.Add(itm);
                lstLanguages.Enabled = false;
            } else {
                lstLanguages.Enabled = true;
            }

            lstBundles.Items.Clear();
            cmd.CommandText = "SELECT id, name FROM " + db.TablePrefix + "bundles WHERE application = @p";
            DbDataReader reader = cmd.ExecuteReader();
            while(reader.Read()) {
                ListItem itm = new ListItem(reader["name"].ToString(), reader["id"].ToString());
                itm.Selected = true;
                tempBundles.Add(itm);
            }
            tempBundles.Sort(cmp);
            lstBundles.Items.AddRange(tempBundles.ToArray());
            if(lstBundles.Items.Count == 0) {
                ListItem itm = new ListItem("No bundles found", "");
                itm.Selected = true;
                lstBundles.Items.Add(itm);
                lstBundles.Enabled = false;
            } else {
                lstBundles.Enabled = true;
            }
            reader.Close();
        }
    }

    /// <summary>
    /// Populates the selected languages to different elements on the page
    /// </summary>
    /// <param name="addForm"><c>true</c> if textboxes should be added for each selected language to the 'New Element' form.</param>
    /// <param name="filtersComboLanguage"><c>true</c> if each language should be added to the 'Filters' combobox.</param>
    /// <param name="attachAddListeners"><c>true</c> if the bind statements for the 'find translation' buttons should be returned (needed for initial page load before an AJAX request has occured).</param>
    /// <returns><c>null</c> unless <c>attachAddListeners</c> was true, in which case the listener statements are returned</returns>
    private string PopulateSelectedLanguages(bool addForm, bool filtersComboLanguage, bool attachAddListeners) {
        tblAddResource.Rows.Clear();
        object defaultLang = Session["defaultLanguage"];
        string defaultLanguage;
        if(defaultLang == null) {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = "SELECT defaultlanguage FROM " + db.TablePrefix + "applications WHERE id = @pID";
                cmd.AddParameterWithValue("@pID", radApplication.SelectedValue);
                defaultLanguage = cmd.ExecuteScalar().ToString();
            }
        } else {
            defaultLanguage = Session["defaultLanguage"].ToString();
        }
        Dictionary<string, int> columnIndex = Session["colIndexes"] as Dictionary<string, int>;
        KeyValuePair<string, string>[] addColIndex;
        if(columnIndex == null) {
            columnIndex = new Dictionary<string, int>();
            List<string> tmpLanguages = new List<string>();
            foreach(ListItem itm in lstLanguages.Items) {
                if(itm.Selected) {
                    tmpLanguages.Add(itm.Value);
                }
            }
            tmpLanguages.Sort();
            tmpLanguages.Remove(defaultLanguage);
            tmpLanguages.Insert(0, defaultLanguage);
            string[] languages = tmpLanguages.ToArray();
            addColIndex = new KeyValuePair<string, string>[tmpLanguages.Count];
            for(int i = 0; i < languages.Length; i++) {
                try {
                    addColIndex[i] = new KeyValuePair<string, string>(languages[i], CultureInfo.GetCultureInfo(languages[i]).EnglishName);
                } catch {
                    addColIndex[i] = new KeyValuePair<string, string>(languages[i], languages[i]);
                }
            }
        } else {
            addColIndex = new KeyValuePair<string, string>[columnIndex.Count];
            foreach(KeyValuePair<string, int> lang in columnIndex) {
                try {
                    addColIndex[lang.Value - editButtonColumNo - 1] = new KeyValuePair<string, string>(lang.Key, CultureInfo.GetCultureInfo(lang.Key).EnglishName);
                } catch {
                    addColIndex[lang.Value - editButtonColumNo - 1] = new KeyValuePair<string, string>(lang.Key, lang.Key);
                }
            }
        }
        Session["addColIndex"] = addColIndex;
        KeyValuePair<string, string> currentLanguage;
        StringBuilder listenerBuffer = attachAddListeners ? new StringBuilder() : null;
        if(filtersComboLanguage) {
            cboTranslationFilterLanguage.Items.Clear();
        }
        for(int i = 0; i < addColIndex.Length; i++) {
            currentLanguage = addColIndex[i];
            if(addForm) {
                TableRow row = new TableRow();
                tblAddResource.Rows.Add(row);
                TableCell celLanguage = new TableCell();
                    celLanguage.CssClass = "language";
                    Label lbl = new Label();
                        lbl.ID = "lblDyn" + currentLanguage.Key;
                        lbl.Text = currentLanguage.Value;
                        lbl.CssClass = "block mockLabel";
                    celLanguage.Controls.Add(lbl);
                row.Cells.Add(celLanguage);
                TableCell celResource = new TableCell();
                    Label lblTranslation = new Label();
                        lblTranslation.Text = "Translation";
                        lblTranslation.CssClass = "block plain";
                    celResource.Controls.Add(lblTranslation);
                    TextBox txtTranslation = new TextBox();
                        txtTranslation.ID = "txtDyn" + currentLanguage.Key;
                        lblTranslation.AssociatedControlID = txtTranslation.ID;
                        txtTranslation.TextMode = TextBoxMode.MultiLine;
                        txtTranslation.Rows = 4;
                        txtTranslation.EnableViewState = false;
                        txtTranslation.ValidationGroup = "addelement";
                        txtTranslation.CssClass = "block";
                    celResource.Controls.Add(txtTranslation);
                    HyperLink btnFindResource = new HyperLink();
                        btnFindResource.Text = "Find Similar Translations";
                        btnFindResource.CssClass = "findResource blockTrigger";
                        btnFindResource.ToolTip = "Lookup resources for other languages using the " + currentLanguage.Value + " translation";
                        btnFindResource.NavigateUrl = "#";
                        btnFindResource.ID = "dynfind-" + currentLanguage.Key;
                    celResource.Controls.Add(btnFindResource);
                    Literal imgLoading = new Literal();
                        imgLoading.Text = "<img src=\"images/ajax-small.gif\" alt=\"Searching...\" class=\"ajax ajax-small\" />";
                    celResource.Controls.Add(imgLoading);
                row.Cells.Add(celResource);
                if(currentLanguage.Key == defaultLanguage) {
                    RequiredFieldValidator val = new RequiredFieldValidator();
                        val.ControlToValidate = txtTranslation.ID;
                        val.ErrorMessage = "The default language must be entered";
                        val.SetFocusOnError = true;
                        val.Display = ValidatorDisplay.Dynamic;
                        val.ValidationGroup = "addelement";
                    celLanguage.Controls.Add(val);
                }
                if(attachAddListeners) {
                    Utils.AssignFindSimilarListener(listenerBuffer, btnFindResource.ClientID, txtTranslation.ClientID, celResource.ClientID, currentLanguage.Key, true);
                }
                TableCell celComment = new TableCell();
                    Label lblTranslatorComment = new Label();
                        lblTranslatorComment.Text = "Translator's Comment";
                        lblTranslatorComment.CssClass = "block plain";
                    celComment.Controls.Add(lblTranslatorComment);
                    TextBox txtTranslatorComment = new TextBox();
                        txtTranslatorComment.ID = txtTranslation.ID + "-comment";
                        lblTranslatorComment.AssociatedControlID = txtTranslatorComment.ID;
                        txtTranslatorComment.TextMode = TextBoxMode.MultiLine;
                        txtTranslatorComment.Rows = 4;
                        txtTranslatorComment.EnableViewState = false;
                        txtTranslatorComment.ValidationGroup = "addelement";
                        txtTranslatorComment.CssClass = "block";
                    celComment.Controls.Add(txtTranslatorComment);
                row.Cells.Add(celComment);
            }
            if(filtersComboLanguage) {
                cboTranslationFilterLanguage.Items.Add(new ListItem(currentLanguage.Value, currentLanguage.Key));
            }
        }
        if(attachAddListeners) {
            Utils.AssignEditPOListener(listenerBuffer, btnAddEditPO.ClientID, txtAddElementComment.ClientID);
            jQueryBuffer.Append(listenerBuffer.ToString()); // for subsequent loads with AJAX
            return listenerBuffer.ToString(); // for the initial load
        } else {
            return null;
        }
    }

    /// <summary>
    /// Processes the selected bundles and languages and shows a grid to edit translations for a resource.
    /// Also sets up listeners to block/unclock the UI when AJAX requests are in progress
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnEditResources_Click(object sender, EventArgs e) {
        mltMain.ActiveViewIndex = 2;
        Session.Remove("colIndexes");
        Session.Remove("defaultLanguage");
        Session.Remove("crossTabColumns");
        InitializeDataSource();
        string addFormListeners = PopulateSelectedLanguages(true, true, true);
        cboAddBundleName.DataBind();
        InitializeGridView();
        grdResources.DataBind();
        ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "ajaxBlock", @"$(function(){ var prm = Sys.WebForms.PageRequestManager.getInstance();
            prm.add_endRequest(function() { $.unblockUI(); attachBlockListeners(); });
            prm.add_initializeRequest(function(sender, args) { $.blockUI({ message: $('#loadingMessage') }); });
            attachBlockListeners();
            $().ajaxStop($.unblockUI);
            $.blockUI.defaults.css = {};
            $('#notResponding').click(function() { $.unblockUI(); return false; });
            " + addFormListeners+"});\n", true);
    }

    /// <summary>
    /// Adds a new element from the form.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnAddElement_Click(object sender, EventArgs e) {
        lblAddError.Visible = false;
        string bundleId = cboAddBundleName.SelectedValue,
               elementName = txtAddElementName.Text,
               elementComment = txtAddElementComment.Text,
               resourceID = cboAddResourceId.SelectedValue,
               resourceComment = txtAddResourceComment.Text;
        if(string.IsNullOrEmpty(elementName)) {
            lblAddError.Text = "Element name cannot be empty";
            lblAddError.Visible = true;
        }
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            if(resourceID == "-1") {
                cmd.CommandText = "SELECT MAX(resourceid) FROM " + db.TablePrefix + "resources";
                int maxId = 0;
                using(DbDataReader maxIdReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
                    if(maxIdReader.HasRows) {
                        maxIdReader.Read();
                        int.TryParse(maxIdReader[0].ToString(), out maxId);
                        maxId++;
                    }
                    maxIdReader.Close();
                }
                resourceID = maxId.ToString();
            }

            cmd.Parameters.Clear();
            switch(db.InstanceType) {
                case Database.DatabaseType.MySQL:
                    cmd.CommandText = "INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment) ON DUPLICATE KEY UPDATE translation = @trans, translatorcomment = @comment";
                    break;
                case Database.DatabaseType.SQLServer:
                    cmd.CommandText = "UPDATE " + db.TablePrefix + "resources SET translation = @trans, translatorcomment = @comment WHERE resourceid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)";
                    break;
            }

            cmd.AddParameterWithValue("@tID", resourceID);
            cmd.AddParameterWithValue("@trans", "");
            cmd.AddParameterWithValue("@lang", "");
            cmd.AddParameterWithValue("@comment", "");
            string language;
            foreach(string key in Request.Form.AllKeys) {
                if(!key.EndsWith("comment") && key.Contains("txtDyn") && Request.Form[key].Length > 0) {
                    language = key.Substring(key.LastIndexOf("txtDyn") + 6); // the ID of the textbox has $country-CODE appended to it
                    cmd.Parameters["@trans"].Value = Request.Form[key];
                    cmd.Parameters["@lang"].Value = language;
                    string comment = Request.Form[key + "-comment"];
                    if(string.IsNullOrEmpty(comment)) {
                        cmd.Parameters["@comment"].Value = DBNull.Value;
                    } else {
                        cmd.Parameters["@comment"].Value = comment;
                    }
                    cmd.ExecuteNonQuery();
                }
            }
            if(resourceComment.Length > 0) {
                cmd.Parameters.Clear();
                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        cmd.CommandText = "INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comment) ON DUPLICATE KEY UPDATE comment = @comment";
                        break;
                    case Database.DatabaseType.SQLServer:
                        cmd.CommandText = "UPDATE " + db.TablePrefix + "resourcecomments SET comment = @comment WHERE resourceid = @tID; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comment)";
                        break;
                }
                cmd.AddParameterWithValue("@tID", resourceID);
                cmd.AddParameterWithValue("@comment", resourceComment);
                cmd.ExecuteNonQuery();
            }
            cmd.Parameters.Clear();
            cmd.CommandText = "INSERT INTO " + db.TablePrefix + "elements (applicationid, bundle, elementname, resourceid, comment) VALUES (@pID, @bundle, @element, @tID, @comment)";
            cmd.AddParameterWithValue("@pID", dsMain.SelectParameters["pID"].DefaultValue);
            cmd.AddParameterWithValue("@tID", resourceID);
            cmd.AddParameterWithValue("@bundle", bundleId);
            cmd.AddParameterWithValue("@element", elementName);
            cmd.AddParameterWithValue("@comment", elementComment);
            try {
                cmd.ExecuteNonQuery();
                InitializeDataSource();
                InitializeGridView();
                grdResources.DataBind();

                cboAddBundleName.SelectedIndex = 0;
                txtAddElementName.Text = string.Empty;
                txtAddElementComment.Text = string.Empty;
                txtAddResourceComment.Text = string.Empty;
                PopulateSelectedLanguages(true, false, false);
                pnlAddedElement.Visible = true;
                litAddedElement.Text = string.Format("<p>Added element {0}.</p>", elementName);
                jQueryBuffer.AppendFormat("$('#{0}').slideUp(3000);\n", pnlAddedElement.ClientID)
                            .AppendFormat("$('#{0}').focus();", txtAddElementName.ClientID);
            } catch {
                lblAddError.Text = "Element name already exists";
                lblAddError.Visible = true;
                txtAddElementName.Focus();
                InitializeGridView();
                grdResources.DataBind();
            }
        }
    }

    /// <summary>
    /// Sets up the column generator for the resource grid using the selected languages.
    /// </summary>
    private void InitializeGridView() {
        List<string> tmpLanguages = new List<string>();
        foreach(ListItem itm in lstLanguages.Items) {
            if(itm.Selected) {
                tmpLanguages.Add(itm.Value);
            }
        }
        Dictionary<string, int> columnIndex = new Dictionary<string, int>();
        string defaultLanguage = Session["defaultLanguage"].ToString();
        tmpLanguages.Sort();
        tmpLanguages.Remove(defaultLanguage);
        tmpLanguages.Insert(0, defaultLanguage);
        string[] languages = tmpLanguages.ToArray();
        for(int i = 0; i < languages.Length; i++) {
            columnIndex[languages[i]] = i + editButtonColumNo + 1;
        }
        Session["colIndexes"] = columnIndex;
        grdResources.ColumnsGenerator = new LanguageColumnGenerator(languages, Session["defaultLanguage"].ToString(), true);
    }

    /// <summary>
    /// Initializes the ResourceID and main DataSources taking the selected languages and bundles into account.
    /// </summary>
    private void InitializeDataSource() {
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        FilterGrid();
        using(Database.Command cmd = db.CreateCommand()) {
            string applicationId = radApplication.SelectedValue,
                   displayName = string.Empty,
                   defaultLanguage = string.Empty;
            cmd.CommandText = "SELECT displayname, defaultlanguage FROM " + db.TablePrefix + "applications WHERE id = @pID";
            cmd.AddParameterWithValue("@pID", applicationId);
            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
            if(reader.HasRows) {
                reader.Read();
            }
            displayName = reader["displayname"].ToString();
            defaultLanguage = reader["defaultlanguage"].ToString();
            reader.Close();
            lblEditingResourcesFor.Text = string.Format("Editing Resources for {0}", displayName);
            lblBreadcrumbEnd.Text = lblEditingResourcesFor.Text;

            if(string.IsNullOrEmpty(dsMain.SelectCommand)) {
                StringBuilder sqlBuffer = new StringBuilder();
                foreach(ListItem itm in lstBundles.Items) {
                    if(itm.Selected) {
                        sqlBuffer.Append("'" + itm.Value + "', ");
                    }
                }
                sqlBuffer.Remove(sqlBuffer.Length - 2, 1); // remove last comma
                string bundleSql = sqlBuffer.ToString();
                sqlBuffer.Remove(0, sqlBuffer.Length);
                string crossTabFormat = string.Empty;
                switch(Constants.DatabaseInstanceType) {
                    case Database.DatabaseType.MySQL:
                        crossTabFormat = "MAX(IF(language='{0}',translation,null)) AS '{0}',";
                        cmd.CommandText = @"SELECT DISTINCT resourceid, 
                                            CAST(IF(CHAR_LENGTH(translation) < 15, 
                                                CONCAT(resourceid, ' - ',translation), 
                                                CONCAT(resourceid, ' - ',SUBSTRING(translation,1,12),'...')
                                                ) AS CHAR) 
                                            AS display FROM " + db.TablePrefix + "resources WHERE language = @defaultLang ORDER BY resourceid ASC";
                        break;
                    case Database.DatabaseType.SQLServer:
                        crossTabFormat = "MAX(CASE WHEN language = '{0}' THEN translation ELSE NULL END) AS '{0}',";
                        cmd.CommandText = @"SELECT DISTINCT resourceid, display = CAST(resourceid AS VARCHAR)+' - '+
                                            CASE
                                             WHEN LEN(translation) < 15 THEN translation
                                             ELSE SUBSTRING(translation,1,12)+'...'
                                            END
                                            FROM " + db.TablePrefix + "resources WHERE language = @defaultLang ORDER BY resourceid ASC";
                        break;
                }
                bool includedDefaultLanguage = false;
                foreach(ListItem itm in lstLanguages.Items) {
                    if(itm.Selected) {
                        sqlBuffer.AppendFormat(crossTabFormat, itm.Value);
                        if(itm.Value == defaultLanguage) {
                            includedDefaultLanguage = true;
                        }
                    }
                }
                if(!includedDefaultLanguage) {
                    sqlBuffer.Insert(0, string.Format(crossTabFormat, defaultLanguage));
                }
                sqlBuffer.Remove(sqlBuffer.Length - 1, 1); // remove last comma
                string crossTabColumns = sqlBuffer.ToString();
                Session["crossTabColumns"] = crossTabColumns;
                switch(Constants.DatabaseInstanceType) {
                    case Database.DatabaseType.MySQL:
                        sqlBuffer.Insert(0, @"SELECT e.id, applicationid, bundle, elementname, e.resourceid, e.comment AS elementcomment, c.comment as resourcecomment, b.name AS bundlename,");
                        sqlBuffer.Append(@" FROM ").Append(db.TablePrefix).Append(@"elements AS e
                                            INNER JOIN ").Append(db.TablePrefix).Append(@"resources AS r ON r.resourceid = e.resourceid 
                                            LEFT JOIN ").Append(db.TablePrefix).Append(@"bundles AS b ON b.id = e.bundle
                                            LEFT JOIN ").Append(db.TablePrefix).Append(@"resourcecomments AS c ON r.resourceid = c.resourceid 
                                            WHERE applicationid = ?pID AND bundle IN (").Append(bundleSql).Append(@") 
                                            GROUP BY e.id 
                                            ORDER BY bundle, elementname ");
                        break;
                    case Database.DatabaseType.SQLServer:
                        sqlBuffer.Insert(0, @"SELECT e.id, applicationid, bundle, elementname, e.resourceid, e.comment AS elementcomment, c.comment as resourcecomment, b.name AS bundlename,");
                        sqlBuffer.Append(@" FROM ").Append(db.TablePrefix).Append(@"elements AS e
                                           INNER JOIN ").Append(db.TablePrefix).Append(@"resources AS r ON r.resourceid = e.resourceid
                                           LEFT JOIN ").Append(db.TablePrefix).Append(@"bundles AS b ON b.id = e.bundle
                                           LEFT JOIN ").Append(db.TablePrefix).Append(@"resourcecomments AS c ON r.resourceid = c.resourceid
                                        WHERE applicationid = @pID  AND bundle IN (").Append(bundleSql).Append(@") 
                                        GROUP BY e.id,
                                                 e.resourceid,
                                                 applicationid,
                                                 bundle,
                                                 elementname,
                                                 e.comment,
                                                 c.comment,
                                                 b.name
                                        ORDER BY bundle, elementname");
                        break;
                }
                dsMain.SelectCommand = sqlBuffer.ToString();
                dsBundles.SelectCommand = "SELECT id, name FROM " + db.TablePrefix + "bundles WHERE id IN (" + bundleSql + ")";

                dsMain.SelectParameters.Clear();
                dsMain.SelectParameters.Add("pID", TypeCode.String, applicationId);
                cmd.AddParameterWithValue("@defaultLang", defaultLanguage);
                reader = cmd.ExecuteReader();
                resourceIds.Clear();
                resourceIds.Add(-1, "New Resource");
                while(reader.Read()) {
                    resourceIds.Add(int.Parse(reader["resourceid"].ToString()), reader["display"].ToString());
                }
                reader.Close();
            }
            cboAddBundleName.DataBind();
            cboAddResourceId.DataSource = resourceIds;
            cboAddResourceId.DataTextField = "Value";
            cboAddResourceId.DataValueField = "Key";
            cboAddResourceId.DataBind();
            Session["defaultLanguage"] = defaultLanguage;
        }
    }

    /// <summary>
    /// Records the total number of records on all pages of the GridView
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.SqlDataSourceStatusEventArgs"/> instance containing the event data.</param>
    protected void dsMain_Selected(object sender, SqlDataSourceStatusEventArgs e) {
        Session["resources"] = e.AffectedRows;
    }

    /// <summary>
    /// Handles the PageIndexChanging event of the resource grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewPageEventArgs"/> instance containing the event data.</param>
    protected void grd_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        grdResources.PageIndex = e.NewPageIndex;
        grdResources.DataBind();
    }

    /// <summary>
    /// Registers JQuery listeners and shows the total number of translations if the grid is paged.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void grd_PreRender(object sender, EventArgs e) {
        base.OnPreRender(e);
        GridViewRow pagerRow = grdResources.TopPagerRow;
        Literal lblNumPages = new Literal();
        if(!grdResources.IsEmpty) {
            TableCell cell = pagerRow.Cells[0];
            (cell.Controls[0] as Table).CssClass = "inline";
            if(grdResources.Rows.Count > 0) {
                int numRecords = 0; // SqlDataSource's Select event is not fired when filtering
                int.TryParse(Session["resources"].ToString(), out numRecords);
                lblNumPages.Text = string.Format("<div class=\"itemCount\">Showing {0}-{1} of {2} elements</div>", grdResources.Rows[0].DataItemIndex + 1, grdResources.Rows[grdResources.Rows.Count - 1].DataItemIndex + 1, Math.Min(grdResources.Rows.Count*grdResources.PageCount,numRecords));
                cell.Controls.Add(lblNumPages);
                lblNoMatching.Visible = false;
            }
        } else {
            lblNoMatching.Visible = true;
        }
        if(grdResources.SelectedIndex >= 0 && grdResources.Rows.Count > grdResources.SelectedIndex) {
            GridViewRow row = grdResources.Rows[grdResources.SelectedIndex];
            // The control indexes of the textbox and image buttons to which to attach event listeners
            View view = (row.Cells[2].Controls[1] as MultiView).Views[1];
            TextBox txt = view.FindControl("txtElementComment") as TextBox;
            HyperLink btnEditPO = view.FindControl("btnEditPO") as HyperLink,
                      btnFindSimilar,
                      btnGoogle;
            Utils.AssignEditPOListener(jQueryBuffer, btnEditPO.ClientID, txt.ClientID);
            string rowId,
                   defaultLang = Session["defaultLanguage"].ToString(),
                   defaultLangTxtBoxId = (row.Cells[editButtonColumNo + 1].Controls[0] as MultiView).Views[1].Controls[SelectedRowControlIndexes.txtTranslation].ClientID, // the ID of the textbox containing the source (default) language
                   defaultLangGoogleCode = string.Empty; // Google's code for the default language
            try {
                defaultLangGoogleCode = Utils.ISOCode2GoogleCode(CultureInfo.GetCultureInfo(defaultLang).TwoLetterISOLanguageName);
            } catch { }
            for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
                rowId = row.Cells[i].ClientID;
                view = (row.Cells[i].Controls[0] as MultiView).Views[1];
                txt = view.Controls[SelectedRowControlIndexes.txtTranslation] as TextBox;
                btnFindSimilar = view.Controls[SelectedRowControlIndexes.btnFindSimilar] as HyperLink;
                btnGoogle = view.Controls[SelectedRowControlIndexes.btnGoogle] as HyperLink;
                Utils.AssignFindSimilarListener(jQueryBuffer, btnFindSimilar.ClientID, txt.ClientID, rowId, btnFindSimilar.NavigateUrl.Substring(1), false);
                if(btnGoogle != null && btnGoogle.Enabled) {
                    Utils.AssignGoogleTranslateSimilarListener(jQueryBuffer, btnGoogle.ClientID, defaultLangTxtBoxId, defaultLangGoogleCode, txt.ClientID, btnGoogle.NavigateUrl.Substring(1));
                }
            }
        }
        // the add form is also in the update panel, so re-register the click events
        Utils.AssignEditPOListener(jQueryBuffer, btnAddEditPO.ClientID, txtAddElementComment.ClientID);
        for(int i=0;i<tblAddResource.Rows.Count;i++) {
            TableRow addRow = tblAddResource.Rows[i];
            TableCell celLanguage = addRow.Cells[0];
            Label lbl = celLanguage.Controls[0] as Label;
            string abbrev = lbl.ID.Replace("lblDyn", "");
            TableCell celResource = addRow.Cells[1];
            TextBox addTxt = celResource.Controls[AddFormControlIndexes.txtTranslation] as TextBox;
            HyperLink btnFindResource = celResource.Controls[AddFormControlIndexes.btnFindSimilar] as HyperLink;
            Utils.AssignFindSimilarListener(jQueryBuffer, btnFindResource.ClientID, addTxt.ClientID, celResource.ClientID, abbrev, true);
        }
    }

    /// <summary>
    /// Databinds the BundleName and ResourceID comboboxes and assigns JavaScript listeners.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewRowEventArgs"/> instance containing the event data.</param>
    protected void grd_RowDataBound(object sender, GridViewRowEventArgs e) {
        if(e.Row.RowType == DataControlRowType.DataRow) {
            GridViewRow row = e.Row;
            DataRowView view = row.DataItem as DataRowView;
            DropDownList cboBundleName = row.FindControl("cboBundleName") as DropDownList;
            cboBundleName.DataBind();
            cboBundleName.SelectedValue = view["bundle"].ToString();

            DropDownList cboResourceId = row.FindControl("cboResourceID") as DropDownList;
            cboResourceId.DataSource = resourceIds;
            cboResourceId.DataTextField = "Value";
            cboResourceId.DataValueField = "Key";
            cboResourceId.DataBind();
            cboResourceId.SelectedValue = view["resourceid"].ToString();

            // find the JS callback which is assigned to the 'Change Resource' button
            Button btnChangeResource = row.FindControl("btnChangeResource") as Button;
            string jsChangeResource = ClientScript.GetPostBackClientHyperlink(btnChangeResource, "");
            cboResourceId.Attributes["onchange"] = jsChangeResource; // assign the callback to the dropdown list so a button press is simulated when the index changes

            TextBox txtElementComment = (row.FindControl("txtElementComment") as TextBox);
            if(!String.IsNullOrEmpty(txtElementComment.Text)) {
                row.Cells[2].ToolTip = txtElementComment.Text;
                row.Cells[2].CssClass = "ssCell commentCell";
            }
            TextBox txtResourceComment = (row.FindControl("txtResourceComment") as TextBox);
            if(!String.IsNullOrEmpty(txtResourceComment.Text)) {
                row.Cells[3].ToolTip = txtResourceComment.Text;
                row.Cells[3].CssClass = "ssCell commentCell";
            }
        }
    }

    /// <summary>
    /// Handles the Sorting event of the resource grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewSortEventArgs"/> instance containing the event data.</param>
    protected void grd_Sorting(object sender, GridViewSortEventArgs e) {
        // Multicolumn sort disabled for now
        /*System.Diagnostics.Debug.WriteLine(e.SortDirection + "-" + e.SortExpression);
        string oldExpression = grdResources.SortExpression,
               newExpression = e.SortExpression;
        if(oldExpression.IndexOf(newExpression) < 0) {
            if(oldExpression.Length > 0) {
                e.SortExpression = newExpression + "," + oldExpression;
            } else {
                e.SortExpression = newExpression;
            }
        } else {
            e.SortExpression = oldExpression;
        }*/
    }

    /// <summary>
    /// Handles RowCommand events from the resource grid.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Web.UI.WebControls.GridViewCommandEventArgs"/> instance containing the event data.</param>
    protected void grd_RowCommand(object sender, GridViewCommandEventArgs e) {
        InitializeDataSource();
        InitializeGridView();
        grdResources.DataBind();
        GridViewRow row;  // The row which has clicked was the undatabound one, so use it's index to lookup the new, databound row
        switch(e.CommandName) {
            case "editresource": // 'Edit' button clicked
                int rowIndex = ((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex;
                if(rowIndex < grdResources.Rows.Count) {
                    row = grdResources.Rows[rowIndex];
                    row.CssClass = "ssRowSelected";
                    ToggleEditControls(row, true);
                    grdResources.SelectedIndex = row.RowIndex;
                }
                break;
            case "canceledit":
                row = grdResources.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                ToggleEditControls(row, false);
                grdResources.SelectedIndex = -1;
                break;
            case "changeresource": // index of dropdownlist changed
                row = (e.CommandSource as WebControl).NamingContainer as GridViewRow;
                string selectedResourceID = (row.Cells[editButtonColumNo].FindControl("cboResourceID") as DropDownList).SelectedValue;
                ChangeDisplayedResource(row, selectedResourceID);
                break;
            case "saveresource": // 'Save' button clicked
                row = (e.CommandSource as WebControl).NamingContainer as GridViewRow;
                UpdateResource(row, e.CommandArgument.ToString());
                grdResources.SelectedIndex = -1;
                break;
            case "deleteelement":
                row = grdResources.Rows[((e.CommandSource as WebControl).NamingContainer as GridViewRow).RowIndex];
                row.CssClass = "ssRowSelected";
                ToggleEditControls(row, false);
                (row.FindControl("mltActions") as MultiView).ActiveViewIndex = 2;
                break;
            case "confirmdelete":
                DeleteElement(e.CommandArgument.ToString());
                break;
        }
    }

    /// <summary>
    /// Deletes the specified element.
    /// </summary>
    /// <param name="elementID">The element ID.</param>
    private void DeleteElement(string elementID) {
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = "DELETE FROM " + db.TablePrefix + "elements WHERE applicationid = @pID AND id = @eID";
            cmd.AddParameterWithValue("@pID", dsMain.SelectParameters["pID"].DefaultValue);
            cmd.AddParameterWithValue("@eID", elementID);
            cmd.ExecuteNonQuery();
        }
        grdResources.DataBind();
    }

    /// <summary>
    /// Shows the textbox controls to edit a resource.
    /// </summary>
    /// <param name="row">The row for which to show the controls.</param>
    /// <param name="visible"><c>true</c> to show the controls, <c>false to hide them</c>.</param>
    private void ToggleEditControls(GridViewRow row, bool visible) {
        int oldRowActiveIndex = visible ? 0 : 1, // the view with the labels etc
            newRowActiveIndex = visible ? 1 : 0; // the view with the textboxes, buttons etc
        if(grdResources.SelectedIndex >= 0 && grdResources.Rows.Count > grdResources.SelectedIndex) {
            GridViewRow oldRow = grdResources.SelectedRow;
            for(int i = editButtonColumNo+1; i < row.Cells.Count; i++) {
                (oldRow.Cells[i].Controls[0] as MultiView).ActiveViewIndex = oldRowActiveIndex;
            }
        }

        (row.FindControl("mltBundle") as MultiView).ActiveViewIndex = newRowActiveIndex;
        (row.FindControl("mltElement") as MultiView).ActiveViewIndex = newRowActiveIndex;
        MultiView mltCommands = (row.FindControl("mltActions") as MultiView);
        mltCommands.ActiveViewIndex = newRowActiveIndex;
        row.Cells[2].CssClass = "ssCell"; // remove the comment indicators
        row.Cells[3].CssClass = "ssCell";
        for(int i = editButtonColumNo + 1; i < row.Cells.Count; i++) {
            (row.Cells[i].Controls[0] as MultiView).ActiveViewIndex = newRowActiveIndex;
        }
        if(visible) { // fill the translator comment fields
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                int textBoxControlIndex = (row.Cells[editButtonColumNo + 1].Controls[0] as MultiView).Views[1].Controls.Count - 1;// the comments textbox is the last control in the view
                object resourceid = (mltCommands.FindControl("cboResourceID") as DropDownList).SelectedValue; // don't need it as an int
                object defaultLang = Session["defaultLanguage"];
                string defaultLanguage;
                bool includedDefaultLanguage = false; // Check if the default language is selected in the list. Add it if not
                if(defaultLang == null) {
                    cmd.CommandText = "SELECT defaultlanguage FROM " + db.TablePrefix + "applications WHERE id = @pID";
                    cmd.AddParameterWithValue("@pID", radApplication.SelectedValue);
                    defaultLanguage = cmd.ExecuteScalar().ToString();
                } else {
                    defaultLanguage = Session["defaultLanguage"].ToString();
                }
                StringBuilder sqlLanguageBuffer = new StringBuilder();
                foreach(ListItem itm in lstLanguages.Items) {
                    if(itm.Selected) {
                        sqlLanguageBuffer.AppendFormat("'{0}',", itm.Value);
                        if(itm.Value == defaultLanguage) {
                            includedDefaultLanguage = true;
                        }
                    }
                }
                if(!includedDefaultLanguage) {
                    sqlLanguageBuffer.AppendFormat("'{0}',", defaultLanguage);
                }
                sqlLanguageBuffer.Remove(sqlLanguageBuffer.Length - 1, 1); // remove last comma
                cmd.CommandText = "SELECT language, translatorcomment FROM " + db.TablePrefix + "resources WHERE resourceid = " + resourceid + " AND language IN (" + sqlLanguageBuffer.ToString() + ")";
                DbDataReader reader = cmd.ExecuteReader();
                Dictionary<string, int> columnIndex = Session["colIndexes"] as Dictionary<string, int>;
                object comment;
                while(reader.Read()) {
                    comment = reader["translatorcomment"];
                    ((row.Cells[columnIndex[reader["language"].ToString()]].Controls[0] as MultiView).Views[1].Controls[textBoxControlIndex] as TextBox).Text = comment == null ? "" : comment.ToString();
                }
                reader.Close();
            }
        }
    }

    /// <summary>
    /// Changes the resource selected for an element.
    /// </summary>
    /// <param name="oldRow">The old row.</param>
    /// <param name="selectedResource">The new resource ID.</param>
    private void ChangeDisplayedResource(GridViewRow oldRow, string selectedResource) {
        GridViewRow row = grdResources.Rows[oldRow.RowIndex]; // the index of the old row in the new grid
        ToggleEditControls(row, true);
        (row.Cells[1].FindControl("cboBundleName") as DropDownList).SelectedValue = (oldRow.Cells[1].FindControl("cboBundleName") as DropDownList).SelectedValue;
        (row.Cells[2].FindControl("txtElementName") as TextBox).Text = (oldRow.Cells[2].FindControl("txtElementName") as TextBox).Text;
        (row.Cells[2].FindControl("txtElementComment") as TextBox).Text = (oldRow.Cells[2].FindControl("txtElementComment") as TextBox).Text;
        DropDownList cboResourceID = (row.Cells[editButtonColumNo].FindControl("cboResourceID") as DropDownList);
        cboResourceID.SelectedValue = selectedResource;
        row.CssClass = "ssRowSelected";
        int txtCommentControlIndex = (row.Cells[editButtonColumNo + 1].Controls[0] as MultiView).Views[1].Controls.Count - 1;// the comments textbox is the last control in the view
        if(selectedResource == "-1") {
            View viwEdit;
            for(int i = editButtonColumNo+1; i < row.Cells.Count; i++) {
                viwEdit = (row.Cells[i].Controls[0] as MultiView).Views[1];
                (viwEdit.Controls[SelectedRowControlIndexes.txtTranslation] as TextBox).Text = "";
                (viwEdit.Controls[txtCommentControlIndex] as TextBox).Text = "";
            }
        } else {
            if(db == null || !db.Connected) {
                db = new Database();
                db.OpenConnection();
            }
            using(Database.Command cmd = db.CreateCommand()) {
                cmd.CommandText = string.Format("SELECT {0} FROM {1}resources WHERE resourceid = @tID", Session["crossTabColumns"], db.TablePrefix);
                cmd.AddParameterWithValue("@tID", selectedResource);
                DbDataReader reader = cmd.ExecuteReader();
                Dictionary<string, int> columnIndex = Session["colIndexes"] as Dictionary<string, int>;
                if(reader.HasRows) {
                    reader.Read();
                    object resource;
                    for(int i = 0; i < reader.FieldCount; i++) {
                        resource = reader[i];
                        ((row.Cells[columnIndex[reader.GetName(i).ToString()]].Controls[0] as MultiView).Views[1].Controls[AddFormControlIndexes.txtTranslation] as TextBox).Text = resource == null ? "" : resource.ToString();
                    }
                }
                reader.Close();

                // fill the translators' comment boxes
                cmd.CommandText = cmd.CommandText.Replace("translation", "translatorcomment");
                reader = cmd.ExecuteReader();
                reader.Read();
                object comment;
                for(int i = 0; i < reader.FieldCount; i++) {
                    comment = reader[i];
                    ((row.Cells[columnIndex[reader.GetName(i).ToString()]].Controls[0] as MultiView).Views[1].Controls[txtCommentControlIndex] as TextBox).Text = comment == null ? "" : comment.ToString();
                }
                reader.Close();

                // fill the overall resource comment
                cmd.CommandText = "SELECT comment FROM " + db.TablePrefix + "resourcecomments WHERE resourceid = @tID";
                reader = cmd.ExecuteReader();
                if(reader.HasRows) {
                    reader.Read();
                    (row.Cells[editButtonColumNo].FindControl("txtResourceComment") as TextBox).Text = reader.GetString(0);
                } else {
                    (row.Cells[editButtonColumNo].FindControl("txtResourceComment") as TextBox).Text = string.Empty;
                }
                reader.Close();
            }
        }
    }

    /// <summary>
    /// Changes the resource displayed on the 'New Element' form.
    /// </summary>
    /// <param name="sender">The event source.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void ChangeAddDisplayedResource(object sender, EventArgs e) {
        int selectedResource = int.Parse(cboAddResourceId.SelectedValue);
        InitializeDataSource();
        InitializeGridView();
        grdResources.DataBind();
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            cmd.CommandText = string.Format("SELECT {0} FROM {1}resources WHERE resourceid = @tID", Session["crossTabColumns"], db.TablePrefix);
            cmd.AddParameterWithValue("@tID", selectedResource.ToString());
            Dictionary<string, int> columnIndex = Session["colIndexes"] as Dictionary<string, int>;
            DbDataReader reader = cmd.ExecuteReader();
            if(reader.HasRows) {
                reader.Read();
                object resource;
                for(int i = 0; i < reader.FieldCount; i++) {
                    resource = reader[i];
                    (tblAddResource.Rows[columnIndex[reader.GetName(i).ToString()] - editButtonColumNo - 1].Cells[1].Controls[AddFormControlIndexes.txtTranslation] as TextBox).Text = resource == null ? "" : resource.ToString();
                }
            }
            reader.Close();
            
            // fill the translators' comments
            cmd.CommandText = cmd.CommandText.Replace("translation", "translatorcomment");
            reader = cmd.ExecuteReader();
            reader.Read();
            object comment;
            for(int i = 0; i < reader.FieldCount; i++) {
                comment = reader[i];
                (tblAddResource.Rows[columnIndex[reader.GetName(i).ToString()]-editButtonColumNo-1].Cells[2].Controls[AddFormControlIndexes.txtTranslationComment] as TextBox).Text = comment == null ? "" : comment.ToString();
            }
            reader.Close();

            cmd.CommandText = "SELECT comment FROM " + db.TablePrefix + "resourcecomments WHERE resourceid = @tID";
            reader = cmd.ExecuteReader();
            if(reader.HasRows) {
                reader.Read();
                txtAddResourceComment.Text = reader.GetString(0);
            } else {
                txtAddElementComment.Text = string.Empty;
            }
        }
        cboAddResourceId.SelectedValue = selectedResource.ToString();
    }

    /// <summary>
    /// Updates a resource.
    /// </summary>
    /// <param name="oldRow">The old row.</param>
    /// <param name="id">The id.</param>
    private void UpdateResource(GridViewRow oldRow, string id) {
        GridViewRow newRow = grdResources.Rows[oldRow.RowIndex];
        if(db == null || !db.Connected) {
            db = new Database();
            db.OpenConnection();
        }
        using(Database.Command cmd = db.CreateCommand()) {
            Label lblError = newRow.Cells[editButtonColumNo].FindControl("lblError") as Label;
            string bundleId = (oldRow.Cells[1].FindControl("cboBundleName") as DropDownList).SelectedValue,
                   elementName = (oldRow.Cells[2].FindControl("txtElementName") as TextBox).Text,
                   elementComment = (oldRow.Cells[2].FindControl("txtElementComment") as TextBox).Text,
                   resourceID = (oldRow.Cells[3].FindControl("cboResourceID") as DropDownList).SelectedValue,
                   resourceComment = (oldRow.Cells[3].FindControl("txtResourceComment") as TextBox).Text;

            if(resourceID == "-1") {
                cmd.CommandText = "SELECT MAX(resourceid) FROM " + db.TablePrefix + "resources";
                int maxId = 0;
                using(DbDataReader maxIdReader = cmd.ExecuteReader(CommandBehavior.SingleRow)) {
                    if(maxIdReader.HasRows) {
                        maxIdReader.Read();
                        int.TryParse(maxIdReader[0].ToString(), out maxId);
                        maxId++;
                    }
                    maxIdReader.Close();
                }
                resourceID = maxId.ToString();
            }

            cmd.Parameters.Clear();
            switch(db.InstanceType) {
                case Database.DatabaseType.MySQL:
                    cmd.CommandText = "INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment) ON DUPLICATE KEY UPDATE translation = @trans, translatorcomment = @comment";
                    break;
                case Database.DatabaseType.SQLServer:
                    cmd.CommandText = "UPDATE " + db.TablePrefix + "resources SET translation = @trans, translatorcomment = @comment WHERE resourceid = @tID AND language = @lang; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resources (resourceid, language, translation, translatorcomment) VALUES (@tID, @lang, @trans, @comment)";
                    break;
            }
            cmd.AddParameterWithValue("@tID", resourceID);
            cmd.AddParameterWithValue("@trans", "");
            cmd.AddParameterWithValue("@lang", "");
            cmd.AddParameterWithValue("@comment", "");
            string textBoxID, language;
            Dictionary<string, int> columnIndex = Session["colIndexes"] as Dictionary<string, int>;
            for(int i = editButtonColumNo + 1; i < newRow.Cells.Count; i++) {
                textBoxID = (newRow.Cells[i].Controls[0] as MultiView).Views[1].Controls[1].UniqueID;
                foreach(string key in Request.Form.AllKeys) {
                    if(key == textBoxID && Request.Form[key].Length > 0) { // because the gridview is dynamic, the controls have to be grabbed from Request.Form
                        language = textBoxID.Substring(textBoxID.LastIndexOf("$") + 1); // the ID of the textbox has $country-CODE appended to it
                        cmd.Parameters["@trans"].Value = Request.Form[key];
                        cmd.Parameters["@lang"].Value = language;
                        string comment = Request.Form[textBoxID + "-comment"];
                        if(string.IsNullOrEmpty(comment)) {
                            cmd.Parameters["@comment"].Value = DBNull.Value;
                        } else {
                            cmd.Parameters["@comment"].Value = comment;
                        }
                        cmd.ExecuteNonQuery();
                        break;
                    }
                }
            }
            if(resourceComment.Length > 0) {
                cmd.Parameters.Clear();
                switch(db.InstanceType) {
                    case Database.DatabaseType.MySQL:
                        cmd.CommandText = "INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comment) ON DUPLICATE KEY UPDATE comment = @comment";
                        break;
                    case Database.DatabaseType.SQLServer:
                        cmd.CommandText = "UPDATE " + db.TablePrefix + "resourcecomments SET comment = @comment WHERE resourceid = @tID; IF @@ROWCOUNT = 0 INSERT INTO " + db.TablePrefix + "resourcecomments (resourceid, comment) VALUES (@tID, @comment)";
                        break;
                }
                cmd.AddParameterWithValue("@tID", resourceID);
                cmd.AddParameterWithValue("@comment", resourceComment);
                cmd.ExecuteNonQuery();
            }
            cmd.Parameters.Clear();
            cmd.CommandText = "UPDATE " + db.TablePrefix + "elements SET bundle = @bundle, elementname = @element, resourceid = @tID, comment = @comment WHERE id = @id";
            cmd.AddParameterWithValue("@tID", resourceID);
            cmd.AddParameterWithValue("@bundle", bundleId);
            cmd.AddParameterWithValue("@element", elementName);
            cmd.AddParameterWithValue("@comment", elementComment);
            cmd.AddParameterWithValue("@id", id);
            try {
                cmd.ExecuteNonQuery();
                cboAddResourceId.DataBind();
                grdResources.DataBind();
            } catch {
                ToggleEditControls(newRow, true);
                newRow.CssClass = "ssRowSelected";
                lblError.Text = "Element name already exists";
                lblError.Visible = true;
            }
        }
    }

    #region Filtering
    /// <summary>
    /// Rebinds the grid ( filtering is done during InitializeGridView() ).
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnApplyFilters_Click(object sender, EventArgs e) {
        InitializeDataSource();
        InitializeGridView();
        grdResources.DataBind();
        CancelEditing();
    }

    /// <summary>
    /// Removes all filters.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void btnRemoveFilters_Click(object sender, EventArgs e) {
        txtBundleFilter.Text = string.Empty;
        txtElementFilter.Text = string.Empty;
        txtElementCommentFilter.Text = string.Empty;
        txtResourceCommentFilter.Text = string.Empty;
        txtTranslationFilter.Text = string.Empty;
        cboBundleFilter.SelectedIndex = 0;
        cboElementFilter.SelectedIndex = 0;
        cboElementCommentFilter.SelectedIndex = 0;
        cboResourceCommentFilter.SelectedIndex = 0;
        cboTranslationFilter.SelectedIndex = 0;
        cboTranslationFilterLanguage.SelectedIndex = 0;
        InitializeDataSource();
        InitializeGridView();
        grdResources.DataBind();
        CancelEditing();
    }

    /// <summary>
    /// Shows/Hides the filters panel
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void lnkToggleFilters_Click(object sender, EventArgs e) {
        if(pnlFiltersCollapsable.CssClass == "collapsed") {
            pnlFiltersCollapsable.CssClass = "";
            imgToggleFilters.ImageUrl = ResolveUrl("~/images/arrow_up.gif");
        } else {
            pnlFiltersCollapsable.CssClass = "collapsed";
            imgToggleFilters.ImageUrl = ResolveUrl("~/images/arrow_down.gif");
        }
        InitializeDataSource();
        InitializeGridView();
        grdResources.DataBind();
        CancelEditing();
    }

    /// <summary>
    /// Cancels any editing in progress in the grid and hides all edit controls.
    /// </summary>
    private void CancelEditing() {
        if(grdResources.SelectedIndex >= 0 && grdResources.SelectedIndex < grdResources.Rows.Count) {
            ToggleEditControls(grdResources.Rows[grdResources.SelectedIndex], false);
            grdResources.SelectedIndex = -1;
        }
    }

    /// <summary>
    /// Applies filters to the datasource.
    /// </summary>
    private void FilterGrid() {
        dsMain.FilterParameters.Clear();
        int numFilters = 0;
        StringBuilder filterBuffer = new StringBuilder();
        string bundle = txtBundleFilter.Text,
               element = txtElementFilter.Text,
               elementComment = txtElementCommentFilter.Text,
               resourceComment = txtResourceCommentFilter.Text,
               translation = txtTranslationFilter.Text;
        if(!string.IsNullOrEmpty(bundle)) {
            filterBuffer.Append("bundlename like ");
            switch(cboBundleFilter.SelectedIndex) {
                case 0:
                    filterBuffer.Append("'%{").Append(numFilters++).Append("}%'");
                    break;
                case 1:
                    filterBuffer.Append("'{").Append(numFilters++).Append("}%'");
                    break;
                case 2:
                    filterBuffer.Append("'%{").Append(numFilters++).Append("}'");
                    break;
            }
            dsMain.FilterParameters.Add("bundlename", TypeCode.String, bundle);
        }
        if(!string.IsNullOrEmpty(element)) {
            if(filterBuffer.Length > 0) {
                filterBuffer.Append(" and ");
            }
            filterBuffer.Append("elementname like ");
            switch(cboElementFilter.SelectedIndex) {
                case 0:
                    filterBuffer.Append("'%{").Append(numFilters++).Append("}%'");
                    break;
                case 1:
                    filterBuffer.Append("'{").Append(numFilters++).Append("}%'");
                    break;
                case 2:
                    filterBuffer.Append("'%{").Append(numFilters++).Append("}'");
                    break;
            }
            dsMain.FilterParameters.Add("elementname", TypeCode.String, element);
        }
        if(!string.IsNullOrEmpty(elementComment)) {
            if(filterBuffer.Length > 0) {
                filterBuffer.Append(" and ");
            }
            filterBuffer.Append("elementcomment like ");
            switch(cboElementCommentFilter.SelectedIndex) {
                case 0:
                    filterBuffer.Append("'%{").Append(numFilters++).Append("}%'");
                    break;
                case 1:
                    filterBuffer.Append("'{").Append(numFilters++).Append("}%'");
                    break;
                case 2:
                    filterBuffer.Append("'%{").Append(numFilters++).Append("}'");
                    break;
            }
            dsMain.FilterParameters.Add("elementcomment", TypeCode.String, elementComment);
        }
        if(!string.IsNullOrEmpty(resourceComment)) {
            if(filterBuffer.Length > 0) {
                filterBuffer.Append(" and ");
            }
            filterBuffer.Append("resourcecomment like ");
            switch(cboResourceCommentFilter.SelectedIndex) {
                case 0:
                    filterBuffer.Append("'%{").Append(numFilters++).Append("}%'");
                    break;
                case 1:
                    filterBuffer.Append("'{").Append(numFilters++).Append("}%'");
                    break;
                case 2:
                    filterBuffer.Append("'%{").Append(numFilters++).Append("}'");
                    break;
            }
            dsMain.FilterParameters.Add("resourcecomment", TypeCode.String, resourceComment);
        }
        if(cboTranslationFilter.SelectedIndex == 3) {
            if(filterBuffer.Length > 0) {
                filterBuffer.Append(" and ");
            }
            filterBuffer.Append('[').Append(cboTranslationFilterLanguage.SelectedValue).Append("] IS NULL");
        } else if(!string.IsNullOrEmpty(translation)) {
            if(filterBuffer.Length > 0) {
                filterBuffer.Append(" and ");
            }
            filterBuffer.Append('[').Append(cboTranslationFilterLanguage.SelectedValue).Append("] like ");
            switch(cboTranslationFilter.SelectedIndex) {
                case 0:
                    filterBuffer.Append("'%{").Append(numFilters++).Append("}%'");
                    break;
                case 1:
                    filterBuffer.Append("'{").Append(numFilters++).Append("}%'");
                    break;
                case 2:
                    filterBuffer.Append("'%{").Append(numFilters++).Append("}'");
                    break;
            }
            dsMain.FilterParameters.Add(cboTranslationFilterLanguage.SelectedValue, TypeCode.String, translation);
        }
        dsMain.FilterExpression = filterBuffer.ToString();
    }
    #endregion

    /// <summary>
    /// Switches back to the first form, retaining the selected values.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void lnkNavEditOnline_Click(object sender, EventArgs e) {
        mltMain.ActiveViewIndex = 1;
    }

    /// <summary>
    /// Expands/contracts the grid to fill the whole window
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
    protected void lnkExpandScreen_Click(object sender, EventArgs e) {
        if(fullsizeholder.Attributes["class"] == "ssFullSize") {
            lnkExpandScreen.Text = "Expand to fill window";
            lnkExpandScreen.CssClass = "lnkExpand";
            fullsizeholder.Attributes["class"] = "ssContracted";
        } else {
            lnkExpandScreen.Text = "Contract to normal size";
            lnkExpandScreen.CssClass = "lnkContract";
            fullsizeholder.Attributes["class"] = "ssFullSize";
        }
    }
    
    
}
